package com.thirdgroup.dao.impl;

import com.thirdgroup.dao.PhoneTotalDao;
import com.thirdgroup.dao.base.BaseDAO;
import com.thirdgroup.pojo.vo.PhoneTotalVo;

import java.util.List;

public class PhoneTotalDaoImpl extends BaseDAO<PhoneTotalVo> implements PhoneTotalDao {
    @Override
    public List<PhoneTotalVo> phoneTotal() {
        String sql="SELECT us.customerID, sum.userPhone, sum.duration, sum.total\n" +
                "FROM user_info us,\n" +
                "    (SELECT u.userPhone, SUM(bill.callDuration) AS duration, SUM(bill.phoneCharge) AS total\n" +
                "    FROM user_info u \n" +
                "    INNER JOIN bill_info bill ON u.userPhone = bill.callingPhone\n" +
                "    WHERE bill.createTime >= DATE_SUB(DATE_FORMAT(NOW(), '%Y-%m-01'), INTERVAL 1 MONTH)\n" +
                "        AND bill.createTime < DATE_FORMAT(NOW(), '%Y-%m-01')\n" +
                "    GROUP BY u.userPhone) sum\n" +
                "WHERE us.userPhone = sum.userPhone";
        return super.findAll(sql,null);
    }
}
